package com.foreknow.test;

import java.sql.*;

/**
 * jdbc
 */
public class DBUtil {
    private Connection conn;
    /**
     * 连接数据库
     */
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接Connection
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_dbs","root","123456");
        return conn;
    }

    /**
     * DML
     * insert into emp(ename,job,salary) values(?,?,?)
     */
    public int DmlOperation(String sql,Object...values) throws SQLException {
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        for (int i = 0;i<values.length;i++) {
            preparedStatement.setObject(i + 1,values[i]);
        }
        int i = preparedStatement.executeUpdate();
        return i;
    }

    /**
     * 查询操作
     * select * from business where name=? and pass=?  因为？不是固定的，所以需要使用动态可变参数
     */
    public ResultSet queryInfo(String sql,Object...values) throws SQLException {
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        for (int i = 0;i<values.length;i++) {
            preparedStatement.setObject(i + 1,values[i]);
        }
        ResultSet rs = preparedStatement.executeQuery();
        return rs;
    }

    /**
     * 关闭资源
     */
    public static void main(String[] args) {
        DBUtil dbUtil = new DBUtil();
        try {
            //连接数据库
            dbUtil.getConnection();
            //int isRight = dbUtil.DmlOperation("insert into admin(name,pass) values(?,?)","suhr","111111");
            //int isRight = dbUtil.DmlOperation("delete from admin where id=?",7);
            //System.out.println(isRight);
            ResultSet rs = dbUtil.queryInfo("select * from admin where name=? and pass=?","gibson","xxx");
            while (rs.next()) {
                //获取当前行数据
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String pass = rs.getString("pass");
                System.out.println(id + " " + name + " " + pass);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        try {
//            //1. 加载驱动
//            Class.forName("com.mysql.jdbc.Driver");
//            //2. 获取连接Connection
//            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo_dbs","root","123456");
//            //3. 获取Statement对象主要用于执行静态的SQL语句
//            //Statement stmt = conn.createStatement();
//            //预编译？表示点位符
//            PreparedStatement preparedStatement = conn.prepareStatement("select * from admin where name=? and pass=?");
//            preparedStatement.setString(1,"gibson");
//            preparedStatement.setString(2,"xxx");
//            //4. 操作数据库
//            // ResultSet executeQuery(String sql) 执行给定的SQL语句，该语句返回单个 ResultSet对象。
//            // int executeUpdate(String sql) 执行给定的SQL语句，这可能是 INSERT ， UPDATE ，或 DELETE语句。
//            //int result = stmt.executeUpdate("delete from admin where id=6");
//            //System.out.println(result);
//            //ResultSet结果集
//            ResultSet rs = preparedStatement.executeQuery();
//
//            List<Admin> list = new ArrayList<>();
//            while (rs.next()) {
//                //获取当前行数据
//                int id = rs.getInt("id");
//                String name = rs.getString("name");
//                String pass = rs.getString("pass");
//                Admin admin = new Admin();
//                admin.setId(id);
//                admin.setName(name);
//                admin.setPass(pass);
//                list.add(admin);
//                //System.out.println(id + " " + name + " " + pass);
//            }
//            Admin admin = list.get(0);
//            System.out.println(admin.getId() + " " + admin.getName() + " " + admin.getPass());
//            //关闭资源
//            rs.close();
//            preparedStatement.close();
//            conn.close();
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
    }
}
